#!/usr/bin/env python
#--coding:utf-8--
import sys
import os

sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,and_,func
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine


Base=declarative_base()

#封装了SQLalchemy的类
class SQLBase:
    def __init__(self):
        self.engine = create_engine("mysql+pymysql://root:123456@10.0.0.20:3306/hostmessage?charset=utf8",encoding="utf-8", max_overflow=5)

        Session=sessionmaker(bind=self.engine)
        self.session=Session()
    #创建删除表
    def create_all(self):
        Base.metadata.create_all(self.engine)
    def drop_all(self):
        Base.metadata.drop_all(self.engine)

    #赠，删，改
    def addTable(self,tablename):  #指定表名
        self.Table = tablename  # Table是
    def add(self,**kwargs):
        row1 = self.Table(**kwargs)
        self.session.add(row1)
    #以下filter不指定，为全部影响
    def delete(self,filter=True):
        self.session.query(self.Table).delete()
    def update(self,filter=True,result={}):
        self.session.query(self.Table).filter(filter).update(result)
    def watch(self,filter=True,order_by=None,start=None,end=None):
        ret = self.session.query(self.Table).filter(filter).order_by(order_by).all()[start:end]
        #print(ret)
        return ret
    def watchmul(self,*othertable,filter=True,order_by=None,start=None,end=None):
        ret = self.session.query(self.Table,*othertable).filter(filter).order_by(order_by).all()[start:end]
        return ret
    def watchCol(self,*colname,filter=True,order_by=None,start=None,end=None):
        ret = self.session.query(*colname).filter(filter).all()[start:end]
        return ret
    def commit(self):
        self.session.commit()




#登录本程序的用户表
class Users(Base):
    __tablename__ = "users"
    userid = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), unique=True)
    password = Column(String(37))

    def __repr__(self):
        return "Users<userid=%s,username=%s,password=%s>" % (self.userid, self.username, self.password)

# 服务器组表
class Groups(Base):
    __tablename__ = "groups"
    groupid = Column(Integer, primary_key=True, autoincrement=True)
    groupname = Column(String(50),unique=True)
    Hosts=relationship("Hosts")
    def __repr__(self):
        return "Groups<groupid=%s,groupname=%s>" % (self.groupid, self.groupname)

#服务器表
class Hosts(Base):
    __tablename__ = "hosts"
    hostid = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(50),unique=True)
    ipattr = Column(String(15))
    port = Column(Integer)
    groupid = Column(Integer,ForeignKey('groups.groupid'))
    Groups=relationship("Groups")
    def __repr__(self):
        return "Hosts<hostid=%s,hostname=%s,ipattr=%s,groupid=%s>" % (self.hostid, self.hostname, self.ipattr,self.groupid)





if __name__=="__main__":
    a=SQLBase()
    a.addTable(Hosts)
    a.delete(filter=Hosts.hostname == "mysql")
    a.commit()

